package com.luochen.cdpt.service;

import com.luochen.cdpt.eneity.Page;
import com.luochen.cdpt.eneity.SerialNumber;
import com.luochen.cdpt.repository.ISerialNumRepository;
import com.luochen.cdpt.util.SqlUtil;
import com.luochen.cdpt.util.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.servlet.http.HttpServletRequest;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @Auther: star
 * @Date: 2019/1/25 10:28
 * @Description:
 */
@Service
public class PolicyService {

    @Autowired
    ISerialNumRepository serialNumRepository;


    @Autowired
    EntityManager entityManager;

    /**
     * 获取订单号
     * @return
     */
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public String getOrderNum(String type) {
        //根据当前时间去查有没有数据
        Date date = new Date();
        String time = new SimpleDateFormat("yyyyMMdd").format(date);
        String lshstr;
        String dateStr = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        SerialNumber sbdlsh = serialNumRepository.findBySjAndType(dateStr,type);
        if (null == sbdlsh) {
            //如果没有新增一条
            SerialNumber lshvo = new SerialNumber();
            lshvo.setLsh(1l);
            lshvo.setSj(dateStr);
            lshvo.setType(type);
            serialNumRepository.save(lshvo);
            lshstr = autoGenericCode(String.valueOf(lshvo.getLsh()), 8);
        } else {
            //如果存在更新累加1
            Long lsh = sbdlsh.getLsh() + 1;
            sbdlsh.setLsh(lsh);
            serialNumRepository.save(sbdlsh);
            lshstr = autoGenericCode(String.valueOf(sbdlsh.getLsh()), 8);
        }
        String sbdh = time + lshstr;//8位时间+8位流水号
        return sbdh;
    }

    /**
     * 不够位数的在前面补0，保留num的长度位数字
     * @param code
     * @return
     */
    private String autoGenericCode(String code, int num) {
        String result = "";
        result = String.format("%0" + num + "d", Integer.parseInt(code));
        return result;
    }

    /**
     * 查询结算打包列表
     * @param request
     * @return
     */
    @SuppressWarnings("unchecked")
    public Page qlist(HttpServletRequest request) {
        try {
            String empcde = request.getParameter("empcde");//员工代码
            String empname = request.getParameter("empname");;//员工名字
            String orgcde = request.getParameter("orgcde");//机构代码
            String teamcde = request.getParameter("teamcde");//团队编码
            String condition_sql = "";
            if (!StringUtils.isEmptry(empcde)) {
                condition_sql = " and csalecde= '" + empcde + "'";
            }
            if (!StringUtils.isEmptry(empname)) {
                condition_sql = " and csalename like '%" + empname + "'%";
            }
            if (!StringUtils.isEmptry(orgcde)) {
                condition_sql = " and orgcde = '" + orgcde + "'";
            }
            if (!StringUtils.isEmptry(teamcde)) {
                condition_sql = " and teamcde = '" + teamcde + "'";
            }
            String sql = "SELECT csalecde,csalename,areacode,areaname,teamname," +
                    " teamcde,orgcde,orgname,inscompanycode,inscompanyname," +
                    " sum(djsfee) AS djsfee FROM tb_policy " +
                    " where `status`='3' " + condition_sql +
                    " GROUP BY" +
                    "  csalecde order by tcrttm desc ";
            return getPage(request, sql);
        } catch (Exception e) {
            return Page.error("查询失败");
        }
    }

    /**
     * 查询结算统计列表
     * @param request
     * @return
     */
    public Page queryFinList(HttpServletRequest request) {
        String sql = "SELECT SUM(dgfee) AS dgfee, SUM(dsfee) AS dsfee, ROUND(SUM(dgfee) - SUM(dsfee),2) AS jyfee,DATE_FORMAT(tupdtm,\"%Y%m\") as tjdate \n" +
                " FROM tb_policy where `status`='3' GROUP BY  DATE_FORMAT(tupdtm,\"%Y%m\") ";
        return getPage(request, sql);
    }

    private Page getPage(HttpServletRequest request, String sql) {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        int totalCount = query.getResultList().size();
        SqlUtil.setPageParam(request, query);
        List<Map> list = query.getResultList();
        return Page.of(totalCount, list);
    }
}
